﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

using System.Text.RegularExpressions;

namespace NewContracts
{
    public partial class FormNewContracts : Form
    {
        /* *******************************************************
         *  Date Member >> Start
         *********************************************************/
        private String m_selectDate;
        private String m_paymentExcel;
        private String m_AllFECExcel;
        private String m_strConn;
        DataSetPayment m_dsNewRecords;
        DataSetPayment m_dsDropRecords;
        XPaymentExcel m_xPayment;
        XAllFECExcel m_xAllFEC;
        XDBOp m_xDbOp;

        /* *********************************************************
         * Public Interface
         * *********************************************************/
        public FormNewContracts()
        {
            InitializeComponent();
            m_dsNewRecords = new DataSetPayment();
            m_dsDropRecords = new DataSetPayment();
            m_xAllFEC = new XAllFECExcel();
            m_xPayment = new XPaymentExcel();
            Properties.Settings config = Properties.Settings.Default;
            m_strConn = config.sqlConnectString;
            m_xDbOp = new XDBOp();
            if (m_strConn == "")
            {
                H_MSG_E("[NG] Connect Database Failed. Application will close");
                this.Close();
            }
        }

        /* **************************************************************
         * Private Function
         * **************************************************************/
        private void buttonBrowsePayment_Click(object sender, EventArgs e)
        {
            try
            {
                openFileDialogPayment.DefaultExt = "xls";
                openFileDialogPayment.Filter = "Excel File: *.xls|*.xls";
                openFileDialogPayment.FileName = "";
                DialogResult tmpRlt = openFileDialogPayment.ShowDialog();

                if (tmpRlt == DialogResult.OK)
                {
                    textBoxPayment.Text = openFileDialogPayment.FileName;
                    m_paymentExcel = textBoxPayment.Text.Trim();
                }
            }
            catch (System.Exception ex)
            {
                H_MSG_E("Error :" + ex.Message);
            }
        }

        private void buttonProcessExcel_Click(object sender, EventArgs e)
        {
            //  -------------- check paid&unpaid.xls ------------------
            if (textBoxPayment.Text == "" || textBoxPayment.Text == null)
            {
                H_MSG_E("Warning : Please specify paid & unpaid.xls file");
                return;
            }
            String paymentfileExt = Path.GetExtension(textBoxPayment.Text);
            if (paymentfileExt != ".xls")
            {
                H_MSG_E("Warning : Please specify excel file");
                return;
            }

            // --------------- check AllFEC.xls -----------------------
            if (textBoxAllFEC.Text == "" || textBoxAllFEC.Text == null)
            {
                H_MSG_E("Warning : Please specify All FEC file");
                return;
            }
            String allFECfileExt = Path.GetExtension(textBoxAllFEC.Text);
            if (allFECfileExt != ".xls")
            {
                H_MSG_E("Warning : Please specify excel file");
                return;
            }
            // -------------- Start Processing  ----------------------
            buttonProcessExcel.Enabled = false;

            // ----------------- Process Paid & Unpiad.xls Excel ----
            DataSet dsPayment = new DataSet();
            dsPayment = m_xPayment.processExcel(textBoxPayment.Text.Trim(), dateTimePickerSpecifyDate.Value);

            //------------------Process AllFEC.xls  -----------------
            DataSet dsAllFEC = new DataSet();
            dsAllFEC = m_xAllFEC.processExcel(textBoxAllFEC.Text.Trim(), dateTimePickerSpecifyDate.Value);
           
            // -----Find Out New Records & Dropped Out Records ----
           DataSetNewDropContracts dsNewRecords = m_xDbOp.findNewRecords(dsPayment, dsAllFEC);
            if (dsNewRecords.Tables[0].Rows.Count < 1)
                labelNewRecords.Text = "Message : There is no record for the previous date " +
                    (dateTimePickerSpecifyDate.Value.AddDays(-1)).ToShortDateString();
            dataGridViewNewContracts.DataSource = dsNewRecords;
            dataGridViewNewContracts.DataMember = dsNewRecords.Tables[0].TableName;

            // -----Find out Drop out records
            DataSetNewDropContracts dsDropOutRecords = m_xDbOp.findDropOutRecords(dsPayment, dateTimePickerSpecifyDate.Value);
            if (dsDropOutRecords.Tables[0].Rows.Count < 1)
                labelDropRecords.Text = "Message : There is no record for the previous date " + 
                    (dateTimePickerSpecifyDate.Value.AddDays(-1)).ToShortDateString();

            // -----DataGrid Showing --------------------------------
            dataGridViewPayment.DataSource = dsPayment;
            dataGridViewPayment.DataMember = dsPayment.Tables[0].TableName;
            dataGridViewAllFEC.DataSource = dsAllFEC;
            dataGridViewAllFEC.DataMember = dsAllFEC.Tables[0].TableName;

            dataGridViewDropContracts.DataSource = dsDropOutRecords;
            dataGridViewDropContracts.DataMember = dsDropOutRecords.Tables[0].TableName;
            return;
        }

        private void dateTimePickerSpecifyDate_ValueChanged(object sender, EventArgs e)
        {
            DateTime selectDate;
            DateTime nowDate = DateTime.Now;
            selectDate = dateTimePickerSpecifyDate.Value;
            if (selectDate > nowDate)
            {
                dateTimePickerSpecifyDate.Value = DateTime.Now;
                H_MSG_W("Warning: Please do not select the date beyond today");
                return;
            }
            m_selectDate = selectDate.ToShortDateString();
            return;
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            try
            {
                textBoxPayment.Text = "";
                textBoxAllFEC.Text = "";
                dateTimePickerSpecifyDate.Value = DateTime.Now;
                buttonProcessExcel.Enabled = true;
                buttonConfirmNUpdate.Enabled = true;
                dataGridViewNewContracts.DataSource = null;
                dataGridViewDropContracts.DataSource = null;
                dataGridViewPayment.DataSource = null;
                dataGridViewAllFEC.DataSource = null;
                labelNewRecords.Text = "";
                labelDropRecords.Text = "";
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return;
        }

        private void linkLabelViewFullNewContracts_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
 
        }

        private void buttonConfirmNUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                DataSetPayment dsFinialPayment = new DataSetPayment();
                dsFinialPayment.Tables[0].Merge(m_dsNewRecords.Tables[0]);
                dsFinialPayment.Tables[0].Merge(m_dsDropRecords.Tables[0]);

                dataGridViewDropContracts.DataSource = dsFinialPayment;
                dataGridViewDropContracts.DataMember = dsFinialPayment.Tables[0].TableName;

                using (SqlConnection conn = new SqlConnection(m_strConn))
                {
                    conn.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                    {
                        bulkCopy.DestinationTableName = dsFinialPayment.Tables[0].TableName;
                      
                        bulkCopy.ColumnMappings.Add("Counterparty", "Counterparty");
                        bulkCopy.ColumnMappings.Add("Name", "Name");
                        bulkCopy.ColumnMappings.Add("LogicalNumber", "LogicalNumber");
                        bulkCopy.ColumnMappings.Add("MaturityDate", "MaturityDate");
                        bulkCopy.ColumnMappings.Add("TotAmount", "TotAmount");
                        bulkCopy.ColumnMappings.Add("PaidStatus", "PaidStatus");
                        bulkCopy.ColumnMappings.Add("Localccy", "Localccy");
                        bulkCopy.ColumnMappings.Add("LocalAmount", "LocalAmount");
                        bulkCopy.ColumnMappings.Add("Rate", "Rate");
                        bulkCopy.ColumnMappings.Add("Currency", "Currency");
                        bulkCopy.ColumnMappings.Add("Instrument", "Instrument");
                        bulkCopy.ColumnMappings.Add("UpdateDate", "UpdateDate");

                        bulkCopy.WriteToServer(dsFinialPayment.Tables[0]);
                    }
                    conn.Close();
                    buttonConfirmNUpdate.Enabled = false;
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void buttonBrowseAllFEC_Click(object sender, EventArgs e)
        {
            try
            {
                openFileDialogPayment.DefaultExt = "xls";
                openFileDialogPayment.Filter = "Excel File: *.xls|*.xls";
                openFileDialogPayment.FileName = "";
                DialogResult tmpRlt = openFileDialogPayment.ShowDialog();

                if (tmpRlt == DialogResult.OK)
                {
                    textBoxAllFEC.Text = openFileDialogPayment.FileName;
                    m_AllFECExcel = textBoxAllFEC.Text.Trim();
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Error :" + ex.Message, "Error Message", System.Windows.Forms.MessageBoxButtons.OK,
                    System.Windows.Forms.MessageBoxIcon.Error);
            }
        }

        private void H_MSG_E(String in_msg)
        {
            MessageBox.Show(in_msg, "Error Message",
                  System.Windows.Forms.MessageBoxButtons.OK,
                  System.Windows.Forms.MessageBoxIcon.Error);
        }

        private void H_MSG_W(String in_msg)
        {
            MessageBox.Show(in_msg, "Warning Message",
                  System.Windows.Forms.MessageBoxButtons.OK,
                  System.Windows.Forms.MessageBoxIcon.Error);
        }
    }
}
